package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpExclusiveProduct;
@Repository
public interface TbSpExclusiveProductDao extends PagingAndSortingRepository<TbSpExclusiveProduct, Integer>, JpaSpecificationExecutor<TbSpExclusiveProduct> {

	TbSpExclusiveProduct findByUserCodeAndProductId(String userCode, Integer id);

	
	@Transactional
	@Modifying
	@Query("delete from TbSpExclusiveProduct where productId = ?1 and userCode = ?2")
	void deleteByPidAndUcode(Integer id,String userCode);


	List<TbSpExclusiveProduct> findByProductId(Integer productId);

	@Transactional
	@Modifying
	@Query("delete from TbSpExclusiveProduct where productId = ?1")
	void deleteByProductId(Integer id);

	@Query(value = "select p.ID as productId,p.RISK_CODE as riskCode,p.PRODUCT_CNAME as productCname,c.rate,p.PRODUCT_TYPE from tb_sp_product p,tb_sp_productconfig c where p.ID in (SELECT e.PRODUCT_ID FROM tb_sp_exclusive_product e where e.USER_CODE= ?1  ) and p.ID = c.Product_id and find_in_set(?2,c.Branch_code)<>0 and p.`STATUS` in ('1','3') and c.IS_EXCLUSIVE = 1 and c.`STATUS` = 1 and c.IS_SHOW = 1",nativeQuery=true)
	List<Object[]> findProByUCode(String userCode,String branchCode);

	@Query(value = "select p.ID as productId,p.RISK_CODE as riskCode,p.PRODUCT_CNAME as productCname,c.rate,p.PRODUCT_TYPE from tb_sp_product p,tb_sp_productconfig c where p.ID in (SELECT e.PRODUCT_ID FROM tb_sp_exclusive_product e where e.USER_CODE= ?1  ) and p.ID = c.Product_id and p.risk_code =?2 and find_in_set(?3,c.Branch_code)<>0 and p.`STATUS` in ('1','3') and c.IS_EXCLUSIVE = 1 and c.`STATUS` = 1 and c.IS_SHOW = 1",nativeQuery=true)
	List<Object[]> findProByUCodeAndRiskCode(String userCode,String riskCode,String branchCode);

	@Query(value = "select count(*) from tb_sp_exclusive_product where USER_CODE=?1 ", nativeQuery = true)
	Integer countExclusive(String userCode);
}
